﻿CREATE PROCEDURE [dbo].[utl_CreateMessageLogTable]
@Force BIT=0
AS
BEGIN
	DECLARE @True  AS BIT; SET @True  = 1
	DECLARE @False AS BIT; SET @False = 0
	
	DECLARE @ErrorCode     INT
	DECLARE @RowsAffected  INT
	DECLARE @Message       VARCHAR(MAX)
	
	-- see if message log table already exists.
	IF dbo.fn_TableExists('MessageLog') = @True
	BEGIN
	    IF @Force = @True
	    BEGIN
	        DROP TABLE dbo.MessageLog
	        SELECT @ErrorCode = @@ERROR,
	               @RowsAffected = @@ROWCOUNT
	        IF @ErrorCode <> 0
	        BEGIN
	            SET @Message = 'Unable to drop table ''MessageLog''.'
	            RAISERROR(@Message, 16, 1)
	            RETURN 1
	        END
	    END
	    ELSE
	    BEGIN
	        BEGIN
	        	SET @Message = 'Table ''MessageLog'' already exists.'
	        	RAISERROR(@Message, 16, 1)
	        	RETURN 2
	        END
	    END
	END
	
	-- create the message log table
	CREATE TABLE dbo.MessageLog
	(
		ID             BIGINT IDENTITY(1, 1) NOT NULL,
		Message        VARCHAR(MAX) NOT NULL,
		RowsAffected   INT NULL,
		ErrorCode      INT NULL,
		ProcessName    sysname NULL,
		ProcedureName  sysname NULL,
		CreateDt       DATETIME NOT NULL,
		Instance       UNIQUEIDENTIFIER NULL
	)
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	       
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Unable to create table ''MessageLog''.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 3
	END
	
	-- add a primary key
	ALTER TABLE [dbo].[MessageLog] WITH NOCHECK ADD
	CONSTRAINT [PK_MessageLog] PRIMARY KEY CLUSTERED([ID])
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	       
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Unable to create primary key for table ''MessageLog''.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 4
	END
	
	-- add default constraints
	ALTER TABLE [dbo].[MessageLog] WITH NOCHECK ADD 
	CONSTRAINT [DF_MessageLog_CreateDt] DEFAULT(GETDATE()) FOR [CreateDt]
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	       
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Unable to create default constraint for table ''MessageLog''.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 5
	END
	
	GRANT INSERT ON dbo.MessageLog TO PUBLIC
	
	RETURN 0
END

